package com.avril.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.avril.constants.DBConstants;
import com.avril.entity.Goods;
import com.avril.entity.GoodsType;
import com.avril.util.ErrorManager;

public class GoodsDao {
	SqlManager manage = null;

	public GoodsDao() {
		super();
		manage = SqlManager.createInstance();
		manage.connectDB();
	}

	public boolean addGoods(Goods goods) {
		boolean result = false;
		try {
			Object[] params = new Object[] { goods.getGoodsCode(),
					goods.getGoodsType().getTypeId(), goods.getGoodsName(),
					goods.getInPrice(), goods.getOutPrice(),
					goods.getLocation(), goods.getAmount() };
			String sql = "insert into goods(goodscode,typeid,goodsname,inprice,outprice,location,amount) values(?,?,?,?,?,?,?)";
			result = manage.executeUpdate(sql, params, DBConstants.PSTM_TYPE);
			manage.closeDB();
		} catch (Exception e) {
			ErrorManager.printError("GoodsDao.addGoods", e);
		}
		return result;
	}

	public boolean isExist(String goodsCode, int goodsType) {
		boolean result = false;
		try {
			Object[] params = new Object[] { goodsCode, goodsType };
			String sql = "select * from goods where goodscode = ? and typeid = ?";
			ResultSet rs = manage.executeQuery(sql, params,
					DBConstants.PSTM_TYPE);
			while (rs.next())
				result = true;
			manage.closeDB();
		} catch (Exception e) {
			ErrorManager.printError("GoodsDao.isExist", e);
		}
		return result;
	}

	public Vector<Goods> getAllGoods() {
		String sql = "select g.*,t.typename from goods g ,type t where g.typeid = t.typeid";
		Vector<Goods> vector = new Vector<Goods>();
		ResultSet rs = manage.executeQuery(sql, null, DBConstants.PSTM_TYPE);
		try {
			while (rs.next()) {
				Goods goods = new Goods();
				goods.setGoodsId(rs.getInt(DBConstants.GOODS_ID));
				goods.setGoodsCode(rs.getString(DBConstants.GOODS_CODE));
				goods.setGoodsType(new GoodsType(
						rs.getInt(DBConstants.TYPE_ID), rs
								.getString(DBConstants.TYPE_NAME)));
				goods.setGoodsName(rs.getString(DBConstants.GOODS_NAME));
				goods.setInPrice(rs.getInt(DBConstants.IN_PRICE));
				goods.setOutPrice(rs.getInt(DBConstants.OUT_PRICE));
				goods.setLocation(rs.getString(DBConstants.LOCATION));
				goods.setAmount(rs.getInt(DBConstants.AMOUNT));
				vector.add(goods);
			}
		} catch (SQLException e) {
			ErrorManager.printError("GoodsDao.getAllGoods", e);
		}
		return vector;
	}

	public Vector<String> getAllGoodsName() {
		String sql = "select * from goods";
		Vector<String> vector = new Vector<String>();
		ResultSet rs = manage.executeQuery(sql, null, DBConstants.PSTM_TYPE);
		try {
			while (rs.next()) {
				vector.add(rs.getString(DBConstants.GOODS_NAME));
			}
		} catch (SQLException e) {
			ErrorManager.printError("GoodsDao.getAllGoodsName", e);
		}
		return vector;
	}

	public Vector<Goods> getGoodsByType(String goodsTypeName) {
		String sql = "select g.*,t.typename from goods g ,type t where g.typeid = t.typeid and t.typename = ?";
		Vector<Goods> vector = new Vector<Goods>();
		Object[] params = new Object[] { goodsTypeName };
		ResultSet rs = manage.executeQuery(sql, params, DBConstants.PSTM_TYPE);
		try {
			while (rs.next()) {
				Goods goods = new Goods();
				goods.setGoodsId(rs.getInt(DBConstants.GOODS_ID));
				goods.setGoodsCode(rs.getString(DBConstants.GOODS_CODE));
				goods.setGoodsType(new GoodsType(
						rs.getInt(DBConstants.TYPE_ID), rs
								.getString(DBConstants.TYPE_NAME)));
				goods.setGoodsName(rs.getString(DBConstants.GOODS_NAME));
				goods.setInPrice(rs.getInt(DBConstants.IN_PRICE));
				goods.setOutPrice(rs.getInt(DBConstants.OUT_PRICE));
				goods.setLocation(rs.getString(DBConstants.LOCATION));
				goods.setAmount(rs.getInt(DBConstants.AMOUNT));
				vector.add(goods);
			}
		} catch (SQLException e) {
			ErrorManager.printError("GoodsDao.getGoodsByType", e);
		}
		return vector;
	}

	public Vector<String> getGoodsNameByType(String goodsTypeName) {
		String sql = "select g.* from goods g, type t where t.typeid = g.typeid and t.typename = ?";
		Vector<String> vector = new Vector<String>();
		Object[] params = new Object[] { goodsTypeName };
		ResultSet rs = manage.executeQuery(sql, params, DBConstants.PSTM_TYPE);
		try {
			while (rs.next())
				vector.add(rs.getString(DBConstants.GOODS_NAME));
		} catch (SQLException e) {
			ErrorManager.printError("GoodsDao.getGoodsByType", e);
		}
		return vector;
	}

	public String getGoodsIdByName(String name) {
		String result = "";
		String sql = "select * from goods where goodsname = ?";
		Object[] params = new Object[] { name };
		ResultSet rs = manage.executeQuery(sql, params, DBConstants.PSTM_TYPE);
		try {
			rs.next();
			result = rs.getString(DBConstants.GOODS_ID);
		} catch (SQLException e) {
			ErrorManager.printError("GoodsDao.getGoodsIdByName", e);
		}
		return result;
	}

	public boolean deleteGoods(String key, String value) {
		boolean result = false;
		try {
			String sql = "delete from goods where " + key + "=?";
			Object[] params = new Object[] { value };
			result = manage.executeUpdate(sql, params, DBConstants.PSTM_TYPE);
			manage.closeDB();
		} catch (Exception e) {
			ErrorManager.printError("GoodsDao.deleteGoods", e);
		}
		return result;
	}

	public Goods findGoodsByPropety(String key, String value) {
		String sql = "select * from goods g, type t where g.typeid = t.typeid and "
				+ key + " = ?";
		Object[] params = new Object[] { value };
		ResultSet rs = manage.executeQuery(sql, params, DBConstants.PSTM_TYPE);
		Goods goods = null;
		try {
			if (rs.next()) {
				goods = new Goods();
				goods.setGoodsId(rs.getInt(DBConstants.GOODS_ID));
				goods.setGoodsCode(rs.getString(DBConstants.GOODS_CODE));
				goods.setGoodsType(new GoodsType(
						rs.getInt(DBConstants.TYPE_ID), rs
								.getString(DBConstants.TYPE_NAME)));
				goods.setGoodsName(rs.getString(DBConstants.GOODS_NAME));
				goods.setInPrice(rs.getInt(DBConstants.IN_PRICE));
				goods.setOutPrice(rs.getInt(DBConstants.OUT_PRICE));
				goods.setLocation(rs.getString(DBConstants.LOCATION));
				goods.setAmount(rs.getInt(DBConstants.AMOUNT));
			}
		} catch (SQLException e) {
			ErrorManager.printError("GoodsDao.findGoodsByPropety", e);
		}
		return goods;
	}
}
